package ua.epam.muzalevskiy.traction.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import org.apache.log4j.Logger;
import ua.epam.muzalevskiy.traction.dao.exceptions.NotExistsParameterException;
import ua.epam.muzalevskiy.traction.dao.exceptions.NotUniqueParameterException;
import ua.epam.muzalevskiy.traction.route.Stop;
import ua.epam.muzalevskiy.traction.route.Route;
import ua.epam.muzalevskiy.traction.vehicle.Vehicle;
import ua.epam.muzalevskiy.traction.vehicle.VehicleFactory;
import ua.epam.muzalevskiy.traction.vehicle.VehicleType;

/**
 * <code>RouteDaoImplementation</code> class is implementation of
 * <code>RouteDao</code> interface.
 *
 * @author Muzalevskiy Vitaliy
 */
public class RouteDaoImplementation implements RouteDao {

    private static final Logger LOGGER =
            Logger.getLogger(StopDaoImplementation.class);
    private final ConnectionFactory factory =
            ConnectionFactoryFactory.newConnectionFactory();
    /**
     * Query to DataBase that add new cortege to traction.route table with
     * values that will be initialized in concrete method.
     */
    public static final String ADD_ROUTE_SQL = "INSERT INTO "
            + "traction.route VALUES(?, ?, ?, ?, ?)";
    /** Query to select all routes from DataBase.*/
    public static final String SELECT_ALL_SQL = "SELECT * FROM "
            + "traction.route";
    /** Query to select route with concrete number value.*/
    public static final String SELECT_BY_NUMBER_SQL = "SELECT * FROM "
            + "traction.route WHERE route_number = ?";
    /** Query to select route with concrete ID value.*/
    public static final String SELECT_BY_ID_SQL = "SELECT * FROM "
            + "traction.route WHERE id = ?";
    /** Query to select all bus routes from DataBase.*/
    public static final String SELECT_BUS_ROUTES_SQL = "SELECT * FROM "
            + "traction.route WHERE vehicle_type = 1";
    /** Query to select all trolleybus routes from DataBase.*/
    public static final String SELECT_TROLLEYBUS_ROUTES_SQL = "SELECT * FROM "
            + "traction.route WHERE vehicle_type = 2";
    /** Query to select all tram routes from DataBase.*/
    public static final String SELECT_TRAM_ROUTES_SQL = "SELECT * FROM "
            + "traction.route WHERE vehicle_type = 3";
    /** Query to delete route with concrete ID value(part 1).*/
    public static final String DELETE_BY_ID_SQL1 = "DELETE FROM "
            + "traction.route WHERE id = ?";
    /** Query to delete route with concrete ID value(part 2).*/
    public static final String DELETE_BY_ID_SQL2 = "DELETE FROM "
            + "traction.routes_stops_conection WHERE id = ?";
    /** Query to delete route with concrete ID value(part 3).*/
    public static final String DELETE_BY_ID_SQL3 = "UPDATE "
            + "traction.vehicle_storage SET assigned_to_route = false, "
            + "route_id = null WHERE route_id = ?";
    /** Query to select all stops of current route.*/
    public static final String SELECT_ROUTE_STOPS_SQL = "SELECT * FROM "
            + "traction.routes_stops_conection WHERE route_id = ?";
    /** Query to select all vehicles of current route.*/
    public static final String SELECT_ROUTE_VEHICLES_SQL = "SELECT * FROM "
            + "traction.vehicle_storage WHERE route_id = ?";
    /** Query to add stop to current route(part 1).*/
    public static final String ADD_STOP_SQL1 = "UPDATE "
            + "traction.routes_stops_conection SET stop_number = stop_number + 1 "
            + "WHERE route_id = ? AND stop_number >= ?";
    /** Query to add stop to current route(part 2).*/
    public static final String ADD_STOP_SQL2 = "INSERT INTO "
            + "traction.routes_stops_conection VALUES( ?, ?, ?)";
    /** Query to delete stop from current route(part 1).*/
    public static final String DELETE_STOP_SQL1 = "DELETE FROM "
            + "traction.routes_stops_conection WHERE route_id = ? "
            + "AND stop_id = ?";
    /** Query to delete stop from current route(part 2).*/
    public static final String DELETE_STOP_SQL2 = "UPDATE "
            + "traction.routes_stops_conection SET stop_number = stop_number - 1 "
            + "WHERE route_id = ? AND stop_number >= ?";
    /** Query to select stop number of current route.*/
    public static final String GET_STOP_NUMBER = "SELECT stop_number FROM "
            + "traction.routes_stops_conection WHERE route_number = ? AND stop_id = ?";
    
    public static final String ATTRIBUTE_ID = "id";
    public static final String ATTRIBUTE_ROUTE_NUMBER = "route_number";
    public static final String ATTRIBUTE_VEHICLE_TYPE = "vehicle_type";
    public static final String ATTRIBUTE_COST = "cost";
    public static final String ATTRIBUTE_AVARAGE_SPEED = "avarage_speed";
    public static final String ATTRIBUTE_STOP_NUMBER = "stop_number";
    public static final String ATTRIBUTE_STOP_ID = "stop_id";

    static {
        ConnectionFactoryFactory.setType(ConnectionFactoryFactory.FactoryType.C3P0);
    }

    /**
     * Method need to save information about new route in DataBase.
     * 
     * @param query <code>PreparedStatement</code> value that include all
     *      information that need to be saved in DataBase.
     * @throws SQLException if there was some problems with Query.
     */
    private void doInsert(PreparedStatement query, int routeID, 
            String routeNumber, int vehicleType,double cost,
            double avarageSpeed) throws SQLException {
        query.setInt(1, routeID);
        query.setString(2, routeNumber);
        query.setInt(3, vehicleType);
        query.setDouble(4, cost);
        query.setDouble(5, avarageSpeed);
        query.executeUpdate();
        LOGGER.info("New Route info was put to DataBase! (route_number = "
                + routeNumber + ")");
    }

    /**
     * Transfer <code>VehicleType</code> value to <code>int</code> equals 
     *      type value stored in DataBese. 
     * @param route current route value.
     * @return <code>int</code> that equals to current <code>VehicleType</code>
     * value.
     */
    private int vehicleTypeToInt(Route route) {
        int type = 0;
        switch (route.getVehicleType()) {
            case BUS:
                type = 1;
                break;
            case TROLLEYBUS:
                type = 2;
                break;
            case TRAM:
                type = 3;
                break;
            default:
                throw new IllegalArgumentException();
        }
        return type;
    }

    /**
     * Transfer <code>int</code> value to <code>VehicleType</code> equals 
     *      type value from enumeration. 
     * @param route current route value.
     * @return <code>int</code> that equals to current <code>VehicleType</code>
     * value.
     */
    private VehicleType vehicleTypeToEnum(int type) {
        VehicleType vehicleType = null;
        switch (type) {
            case 1:
                vehicleType = VehicleType.BUS;
                break;
            case 2:
                vehicleType = VehicleType.TROLLEYBUS;
                break;
            case 3:
                vehicleType = VehicleType.TRAM;
                break;
            default:
                throw new IllegalArgumentException();
        }
        return vehicleType;
    }

    /**
     * Method for getting information about route from DataBase.
     * @param result  <code>ResultSet</code> of concrete query.
     * @return <code>Route</code> value.
     * @throws SQLException if there was some problems with Query. 
     */
    private Route createRoute(ResultSet result) throws SQLException {
        Route route;
        List<Stop> stops;
        List<Vehicle> vehicleList;
        int routeID = result.getInt(ATTRIBUTE_ID);
        String routeNumber = result.getString(ATTRIBUTE_ROUTE_NUMBER);
        VehicleType type = vehicleTypeToEnum(result.getInt(ATTRIBUTE_VEHICLE_TYPE));
        double cost = result.getDouble(ATTRIBUTE_COST);
        double avarageSpeed = result.getDouble(ATTRIBUTE_AVARAGE_SPEED);
        route = new Route(routeID, routeNumber, type, cost, avarageSpeed);
        stops = getRouteStops(routeID);
        vehicleList = getRouteVehicles(routeID);
        route.setStops(stops);
        route.setTransportList(vehicleList);
        LOGGER.debug("Route info was taken from DataBase! (route_number = "
                + routeNumber + ")");
        return route;
    }

    /**
     * Method need to get some list of routes.
     * @param sql <code>String</code> value of query with needed parameters.
     * @return <code>List</code> value with needed routes.
     */
    private List<Route> selectListOfRoutes(String sql) {
        List<Route> list = new ArrayList<Route>();
        Route route;
        try {
            Connection myConnection = factory.newConnection();
            try {
                Statement statement = myConnection.createStatement();

                ResultSet result = statement.executeQuery(sql);
                while (result.next()) {
                    route = createRoute(result);
                    list.add(route);
                }

            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return list;
    }

    @Override
    public void addRoute(Route route) throws NotUniqueParameterException {
        int type = vehicleTypeToInt(route);
        Connection myConnection = null;
        try {
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection.prepareStatement(ADD_ROUTE_SQL);

                myConnection.setAutoCommit(false);
                if (selectRouteByID(route.getID()) != null) {
                    throw new NotUniqueParameterException();
                }
                try {
                    doInsert(query, route.getID(), route.getNumber(), type, 
                            route.getCost(), route.getAvarageSpeed());
                    myConnection.commit();
                } catch (SQLException ex) {
                    LOGGER.warn(ex.getMessage());
                    LOGGER.info("Trtansaction is rolled back!");
                    myConnection.rollback();
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
    }

    @Override
    public List<Route> selectAll() {
        return selectListOfRoutes(SELECT_ALL_SQL);
    }

    @Override
    public Route selectRouteByNumber(String routeNumber) {
        Route route = null;
        Connection myConnection = null;
        try {
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(SELECT_BY_NUMBER_SQL);
                query.setString(1, routeNumber);
                ResultSet result = query.executeQuery();
                while (result.next()) {
                    route = createRoute(result);
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return route;
    }
    
    @Override
    public Route selectRouteByID(int routeID) {
        Route route = null;
        Connection myConnection = null;
        try {
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(SELECT_BY_ID_SQL);
                query.setInt(1, routeID);
                ResultSet result = query.executeQuery();
                while (result.next()) {
                    route = createRoute(result);
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return route;
    }

    @Override
    public List<Route> selectBusRoutes() {
        return selectListOfRoutes(SELECT_BUS_ROUTES_SQL);
    }

    @Override
    public List<Route> selectTrolleybusRoutes() {
        return selectListOfRoutes(SELECT_TROLLEYBUS_ROUTES_SQL);
    }

    @Override
    public List<Route> selectTramRoutes() {
        return selectListOfRoutes(SELECT_TRAM_ROUTES_SQL);
    }

    @Override
    public void deleteRouteByID(int route_id)
            throws NotExistsParameterException {
        Connection myConnection = null;
        try {
            try {
                myConnection = factory.newConnection();
                PreparedStatement query1 = myConnection
                        .prepareStatement(DELETE_BY_ID_SQL1);
                PreparedStatement query2 = myConnection
                        .prepareStatement(DELETE_BY_ID_SQL2);
                PreparedStatement query3 = myConnection
                        .prepareStatement(DELETE_BY_ID_SQL3);
                myConnection.setAutoCommit(false);
                Route route = selectRouteByID(route_id);
                if (route == null) {
                    throw new NotExistsParameterException();
                }
                query1.setInt(1, route_id);
                query2.setInt(1, route_id);
                query3.setInt(1, route_id);
                query1.executeUpdate();
                query2.executeUpdate();
                query3.executeUpdate();
                query1.close();
                query2.close();
                query3.close();
                myConnection.commit();
                LOGGER.info("Route info was cleared from DataBase! (route_id = "
                        + route_id + ")");
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
    }

    @Override
    public List<Stop> getRouteStops(int routeNumber) {
        DaoFactory daoFactory = new DaoFactory();
        StopDao stopDao = daoFactory.getStopDao();
        List<Stop> list = new LinkedList<Stop>();
        Stop stop;
        Connection myConnection = null;
        try {
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(SELECT_ROUTE_STOPS_SQL);
                myConnection.setAutoCommit(false);
                query.setInt(1, routeNumber);
                ResultSet result = query.executeQuery();
                while (result.next()) {
                    int stopNumber = result.getInt(ATTRIBUTE_STOP_NUMBER);
                    int stopID = result.getInt(ATTRIBUTE_STOP_ID);
                    stop = stopDao.selectStopByID(stopID);
                    list.add(stopNumber, stop);
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return list;
    }

    @Override
    public List<Vehicle> getRouteVehicles(int routeID) {
        List<Vehicle> list = new ArrayList<Vehicle>();
        Vehicle vehicle = null;
        Connection myConnection = null;
        try {
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(SELECT_ROUTE_VEHICLES_SQL);
                query.setInt(1, routeID);
                ResultSet result = query.executeQuery();
                while (result.next()) {
                    int vehicleID = result.getInt(
                            VehicleDaoImplementation.ATTRIBUTE_ID);
                    String vehicleNum = result.getString(
                            VehicleDaoImplementation.ATTRIBUTE_VEHICLE_NUMBER);
                    int vehicleType = result.getInt(
                            VehicleDaoImplementation.ATTRIBUTE_VEHICLE_TYPE);
                    boolean assignedToRoute = result.getBoolean(
                            VehicleDaoImplementation.ATTRIBUTE_ASSIGNED_TO_ROUTE);
                    int routeNum = result.getInt(
                            VehicleDaoImplementation.ATTRIBUTE_ROUTE_ID);
                    switch (vehicleType) {
                        case 1:
                            vehicle = new VehicleFactory().createVehicle(
                                    vehicleID, vehicleNum, VehicleType.BUS);
                            vehicle.setAssignedToRoute(assignedToRoute);
                            vehicle.setRouteID(routeNum);
                            break;
                        case 2:
                            vehicle = new VehicleFactory().createVehicle(
                                    vehicleID, vehicleNum, VehicleType.TROLLEYBUS);
                            vehicle.setAssignedToRoute(assignedToRoute);
                            vehicle.setRouteID(routeNum);
                            break;
                        case 3:
                            vehicle = new VehicleFactory().createVehicle(
                                    vehicleID, vehicleNum, VehicleType.TRAM);
                            vehicle.setAssignedToRoute(assignedToRoute);
                            vehicle.setRouteID(routeNum);
                            break;
                        default:
                            throw new SQLException();
                    }
                    list.add(vehicle);
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return list;
    }

    @Override
    public void addStop(int routeID, int number, int stopID) {
        Connection myConnection = null;
        try {
            try {
                myConnection = factory.newConnection();
                PreparedStatement query1 = myConnection
                        .prepareStatement(ADD_STOP_SQL1);
                PreparedStatement query2 = myConnection
                        .prepareStatement(ADD_STOP_SQL2);
                myConnection.setAutoCommit(false);
                query1.setInt(1, routeID);
                query1.setInt(2, number);
                query2.setInt(1, routeID);
                query2.setInt(2, number);
                query2.setInt(3, stopID);
                query1.executeUpdate();
                query2.executeUpdate();
                myConnection.commit();
                LOGGER.info("Route info was changed! (route_id = "
                        + routeID + ")");
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
    }

    @Override
    public void deleteStop(int routeID, int stopID) {
        try {
            Connection myConnection = null;
            try {
                myConnection = factory.newConnection();
                PreparedStatement query1 = myConnection
                        .prepareStatement(DELETE_STOP_SQL1);
                PreparedStatement query2 = myConnection
                        .prepareStatement(DELETE_STOP_SQL2);
                myConnection.setAutoCommit(false);
                int number = getStopNumber(routeID, stopID);
                query1.setInt(1, routeID);
                query1.setInt(2, stopID);
                query2.setInt(1, routeID);
                query2.setInt(2, number);
                query1.executeUpdate();
                query2.executeUpdate();
                myConnection.commit();
                LOGGER.info("Route info was changed! (route_id = "
                        + routeID + ")");
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
    }

    @Override
    public Integer getStopNumber(int routeID, int stopID) {
        Integer number = null;
        Connection myConnection = null;
        try {
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(GET_STOP_NUMBER);
                query.setInt(1, routeID);
                query.setInt(2, stopID);
                ResultSet result = query.executeQuery();
                while (result.next()) {
                    number = result.getInt(ATTRIBUTE_STOP_NUMBER);
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        LOGGER.debug("Route info was taken from DataBase! (route_id = "
                + routeID + ")");
        return number;
    }
}
